package ua.pp.shell.wka;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import ua.pp.shell.wka.DBAdapter.Types.Vocabulary;
import ua.pp.shell.wka.DBAdapter.Types.VocabularyStatistic;
import ua.pp.shell.wka.DBAdapter.Types.Word;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class DBAdapter extends SQLiteOpenHelper {
	
	static public final class Types {
		
		static public class Vocabulary {
			public int id = -1;
			public String title = "";
			public String language_from = "";
			public String language_to = "";
			public String description = "";
			
			public Vocabulary(int id, String title, String language_from, String language_to, String description) {
				this.id = id;
				this.title = title;
				this.language_from = language_from;
				this.language_to = language_to;
				this.description = description;
			}
			
			public Vocabulary() {
				
			}
			
			public Vocabulary(Cursor cursor) {
				FillFromCursor(cursor);
			}
			
			public void FillFromCursor(Cursor cursor) {
				id = cursor.getInt(cursor.getColumnIndex("id")); 
				title = cursor.getString(cursor.getColumnIndex("title"));
				language_from = cursor.getString(cursor.getColumnIndex("language_from"));
				language_to = cursor.getString(cursor.getColumnIndex("language_to"));
				description = cursor.getString(cursor.getColumnIndex("description"));
			}
		}
		
		static public class VocabularyStatistic {
			public int words = 0;
			public int knowledge = 0;
			public int percentage = 0;
			
			public VocabularyStatistic(int words, int knowledge) {
				Fill(words, knowledge);
			}
			
			public VocabularyStatistic(Cursor cursor) {
				FillFromCursor(cursor);
			}
			
			public void Fill(int words, int knowledge) {
				this.words = words;
				this.knowledge = knowledge;
				percentage = Math.round((knowledge / (float)words) * 100);
			}
			
			public void FillFromCursor(Cursor cursor) {
				words = cursor.getInt(cursor.getColumnIndex("words"));
				knowledge = cursor.getInt(cursor.getColumnIndex("knowledge"));
				Fill(words, knowledge);
			}
		}
		
		static public class Word {
			public int id = -1;
			public int id_vocabulary = -1;
			public String word = "";
			public String translation = "";
			public long t_created = -1;
			public long t_last_seen = -1;
			public long t_last_success = -1;
			public long t_last_error = -1;
			public int c_shown = 0;
			public int c_success = 0;
			public int c_error = 0;
			
			public Word() {
				
			}
			
			public Word(Cursor cursor) {
				FillFromCursor(cursor);
			}
			
			public Word(int id, int id_vocabulary, String word, String translation,
					long t_created, long t_last_seen, long t_last_success, long t_last_error, 
					int c_shown, int c_success, int c_error) {
				this.id = id;
				this.id_vocabulary = id_vocabulary;
				this.word = word;
				this.translation = translation;
				this.t_created = t_created;
				this.t_last_error = t_last_error;
				this.t_last_seen = t_last_seen;
				this.t_last_success = t_last_success;
				this.c_shown = c_shown;
				this.c_success = c_success;
				this.c_error = c_error;
			}
			
			public void FillFromCursor(Cursor cursor) {
				id = cursor.getInt(cursor.getColumnIndex("id"));
				id_vocabulary = cursor.getInt(cursor.getColumnIndex("id_vocabulary"));
				word = cursor.getString(cursor.getColumnIndex("word"));
				translation = cursor.getString(cursor.getColumnIndex("translation"));
				t_created = cursor.getLong(cursor.getColumnIndex("t_created"));
				t_last_seen = cursor.getLong(cursor.getColumnIndex("t_last_seen")); 
				t_last_success = cursor.getLong(cursor.getColumnIndex("t_last_success"));
				t_last_error = cursor.getLong(cursor.getColumnIndex("t_last_error"));
				c_shown = cursor.getInt(cursor.getColumnIndex("c_shown"));
				c_success = cursor.getInt(cursor.getColumnIndex("c_success"));
				c_error = cursor.getInt(cursor.getColumnIndex("c_error"));
			}
		}
	}
		
 	private static class SQL {
		
 		public static String create_vocabulary_table = "" +
			"CREATE TABLE `vocabularies`(" +
			"	`id`    		INTEGER PRIMARY KEY AUTOINCREMENT," +
			"	`title`  		TEXT NOT NULL," +
			"	`language_from`	TEXT NOT NULL," +
			"	`language_to`	TEXT NOT NULL," +
			"	`description`	TEXT" +
			");";
		
		public static String create_word_table = "" +
			"CREATE TABLE `words`("+
			"	`id`    			INTEGER PRIMARY KEY AUTOINCREMENT,"+
			"	`id_vocabulary`    	INTEGER  NOT NULL,"+
			"	`word`				TEXT NOT NULL,"+
			"	`translation`		TEXT NOT NULL,"+
			"	`t_created`			NUMERIC NOT NULL,"+
			"	`t_last_seen`		NUMERIC NOT NULL,"+
			"	`t_last_success`	NUMERIC NOT NULL,"+
			"	`t_last_error`		NUMERIC NOT NULL,"+
			"	`c_shown`			NUMERIC NOT NULL,"+
			"	`c_success`			NUMERIC NOT NULL,"+
			"	`c_error`			NUMERIC NOT NULL,"+
			"	FOREIGN KEY(id_vocabulary) REFERENCES vocabularies(id) ON DELETE CASCADE  ON UPDATE CASCADE"+
			");";
		
		public static String select_vocabulary_table = "SELECT * FROM `vocabularies`";
		public static String select_random_words_from_vocabulary = "SELECT * FROM `words`  WHERE `id_vocabulary` = ?  ORDER BY RANDOM() LIMIT ?";
		public static String select_words_from_vocabulary_by_knowledge = ""+
				"SELECT * FROM (" +
				"	SELECT"+
				" 	*,"+
				" 	((CASE WHEN c_shown < 4 THEN 1 ELSE 0 END) * 10 + c_shown * -1 + c_success * -1 + c_error * 2 ) as power " +
				" 	FROM words" +
				" 	WHERE `id_vocabulary` = ?" +
				" 	ORDER BY power DESC" +
				" 	LIMIT ?" +
				") ORDER BY RANDOM()" +
				"LIMIT ?";
		
		public static String select_vocabulary_statistic = "" +
				" SELECT " +
				"	COUNT(*) as `words`, " +
				"	SUM(CASE WHEN `t_last_success` > `t_last_error` THEN 1 ELSE 0 END) as `knowledge`" +
				" FROM `words` " +
				" WHERE `id_vocabulary` = ?" +
				" LIMIT 1";
		public static String select_one_word_table = "SELECT * FROM `words` WHERE `id` = ? LIMIT 1";
		public static String select_one_vocabulary_table = "SELECT * FROM `vocabularies` WHERE `id` = ? LIMIT 1";
	}
 	
 	private static final String DB_NAME = "wka";
 	private static final int DB_VERSION = 1;
 	private static final CursorFactory DB_CF = null;
 	
 	private SQLiteDatabase db = null;
 	 
 	public SQLiteDatabase OpenRDb() {
 		CloseDb();
 		db = getReadableDatabase();
 		return db;
 	}
 	
 	public SQLiteDatabase OpenWDb() {
 		CloseDb();
 		db = getWritableDatabase();
 		return db;
 	}
 	
 	public void CloseDb() {
 		if (db == null) return;
 		db.close();
 	}
 	
 	public DBAdapter(Context context) {
 		super(context, DB_NAME, DB_CF, DB_VERSION);
 	}
 	
	public DBAdapter(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL.create_vocabulary_table);
		db.execSQL(SQL.create_word_table);
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {	
	}
	
	
	public List<Word> GetRandomWordsFromVovabulary(int id_vocabulary, int limit) {
		List<Word> words = new ArrayList<Word>();
		OpenRDb();
		Cursor cursor = db.rawQuery(SQL.select_random_words_from_vocabulary, new String[]{Integer.toString(id_vocabulary), Integer.toString(limit)});	
		cursor.moveToFirst();
		for(int i=0; i < cursor.getCount(); i++) {
			words.add(new Word(cursor));
			cursor.moveToNext();
		}
		CloseDb();
		return words;
	}
	
	public List<Word> GetWordsFromVocabularyByKnowledge(int id_vocabulary, int count_words, int limit) {
		List<Word> words = new ArrayList<Word>();
		OpenRDb();
		Cursor cursor = db.rawQuery(SQL.select_words_from_vocabulary_by_knowledge, new String[]{Integer.toString(id_vocabulary), Integer.toString(count_words), Integer.toString(limit)});	
		cursor.moveToFirst();
		for(int i=0; i < cursor.getCount(); i++) {
			words.add(new Word(cursor));
			cursor.moveToNext();
		}
		CloseDb();
		return words;
	}
	
	public VocabularyStatistic GetVocabularyStatistic(int id_vocabulary) {
		OpenRDb();
		Cursor cursor = db.rawQuery(SQL.select_vocabulary_statistic, new String[]{Integer.toString(id_vocabulary)});	
		cursor.moveToFirst();
		VocabularyStatistic result = new VocabularyStatistic(cursor);
		CloseDb();
		return result;
	}
	
	public ContentValues PrepareVocabulary(Vocabulary vocabulary) {
		ContentValues values = new ContentValues();
		values.put("title", vocabulary.title);
		values.put("language_from", vocabulary.language_from);
		values.put("language_to", vocabulary.language_to);
		values.put("description", vocabulary.description);
		return values;
	}
	
	public Vocabulary GetVocabulary(int id) {
		OpenRDb();
		Cursor cursor = db.rawQuery(SQL.select_one_vocabulary_table, new String[]{Integer.toString(id)});	
		cursor.moveToFirst();
		Vocabulary result = new Vocabulary(cursor);
		CloseDb();
		return  result;
	}
	
	public void DeleteVocabulary(int id) {
		OpenWDb();
		db.delete("vocabularies", "id=?", new String[] { Integer.toString(id) });
		CloseDb();
	}
	
	public int UpdateVocabulary(Vocabulary vocabulary) {
		OpenWDb();
		int result = db.update("vocabularies", PrepareVocabulary(vocabulary), "id=?", new String[]{String.valueOf(vocabulary.id)});
		CloseDb();
		return result;
	}
	
	public int InsertVocabulary(Vocabulary vocabulary) {
		OpenWDb();
		int result = (int)db.insert("vocabularies", null, PrepareVocabulary(vocabulary));
		CloseDb();
		return result;
	}
	
	public List<Vocabulary> GetVocabularies() {
		List<Vocabulary> vocabularies = new ArrayList<Vocabulary>();
		OpenRDb();
		Cursor cursor = db.rawQuery(SQL.select_vocabulary_table, null);	
		cursor.moveToFirst();
		for(int i=0; i < cursor.getCount(); i++) {
			vocabularies.add(new Vocabulary(cursor));
			cursor.moveToNext();
		}
		CloseDb();
		return vocabularies;
	}
	
	public void DeleteWord(int id) {
		OpenWDb();
		db.delete("words", "id=?", new String[] { Integer.toString(id) });
		CloseDb();
	}
	
	public ContentValues PrepareWord(Word word) {
		ContentValues values = new ContentValues();
		values.put("id_vocabulary", word.id_vocabulary);
		values.put("word", word.word);
		values.put("translation", word.translation);
		values.put("c_error", word.c_error);
		values.put("c_shown", word.c_shown);
		values.put("c_success", word.c_success);
		values.put("t_created", word.t_created);
		values.put("t_last_seen", word.t_last_seen);
		values.put("t_last_error", word.t_last_error);
		values.put("t_last_success", word.t_last_success);
		return values;
	}
	
	public int InsertWord(Word word) {
		Date date = new Date();
		word.t_created = date.getTime();
		OpenWDb();
		int result = (int) db.insert("words", null, PrepareWord(word));
		CloseDb();
		return result;
	}
	
	public int UpdateWord(Word word) {
		OpenWDb();
		int rows = db.update("words", PrepareWord(word), "id=?", new String[]{String.valueOf(word.id)});
		CloseDb();
		return rows;
	}
	
	public List<Word> GetWords(int vocabulary_id, String title_filter, int from, int count) {
		List<Word> words = new ArrayList<Word>(); 
		OpenRDb();
		String selection = "id_vocabulary = ? ";
		ArrayList<String> selectionArgs = new ArrayList<String>();
		selectionArgs.add(Integer.toString(vocabulary_id));
		if (title_filter.trim().length() != 0 ) {
			selection += " AND (`word` LIKE ? OR `translation` LIKE ?) ";
			selectionArgs.add('%'+title_filter+'%');
			selectionArgs.add('%'+title_filter+'%');
		}
		String[] select = {"*"};
		String limit = String.format("%d, %d", from, count);
		Cursor cursor = db.query( "words", select, selection, (String[])selectionArgs.toArray(new String[0]), null, null, null, limit);
		cursor.moveToFirst();
		for(int i=0; i < cursor.getCount(); i++) {
			words.add(new Word(cursor));
			Log.v("DATA", cursor.getString(0) + " " +cursor.getString(1)+ " " +cursor.getString(2));
			cursor.moveToNext();
		}
		CloseDb();
		return words;
	}
	
	public int CountWords(int vocabulary_id, String title_filter) {
		OpenRDb();
		String selection = "id_vocabulary = ? ";
		ArrayList<String> selectionArgs = new ArrayList<String>();
		selectionArgs.add(Integer.toString(vocabulary_id));
		if (title_filter.trim().length() != 0 ) {
			selection += " AND word LIKE ?";
			selectionArgs.add('%'+title_filter+'%');
		}
		String[] select = {"COUNT (*) as count, id"};
		String limit = "1";
		Cursor cursor = db.query( "words", select, selection, (String[])selectionArgs.toArray(new String[0]), null, null, null, limit);
		cursor.moveToFirst();
		int count = cursor.getInt(cursor.getColumnIndex("count")); 
		return count;
	}
	
	public Word GetWord(int word_id) {
		OpenRDb();
		Cursor cursor = db.rawQuery(SQL.select_one_word_table, new String[]{Integer.toString(word_id)});
		cursor.moveToFirst();
		Word result = new Word(cursor);
		CloseDb();
		return  result;
	}
}